const db = require("../../lib/conn_oracle")

exports.getLbPlan = async (req,res)=>{
    // console.log(req.body)
    let {form } = req.body
    let {planYear,quarter,planStatus,outputStatus,signStatus,persongroup,realname} = form

    let resMsg = ""
    let resStatus = 0
    let resDatas = {}
    let reqDatas = []

    let quarterWhere = ""
    let planStatusWhere = ""
    let outputStatusWhere = ""
    let signStatusWhere = ""
    let persongroupWhere = ""
    let realnameWhere = ""

    if(planYear) reqDatas.push(planYear) 
    if(quarter.length) {
        quarterWhere = `and p.quarter = :quarter`
        reqDatas.push(quarter)
    }
    if(planStatus === 0 || planStatus === 1) {
        planStatusWhere = `and p.plan_status = :planStatus`
        reqDatas.push(planStatus)
    }
    if(outputStatus === 0 || outputStatus === 1) {
        outputStatusWhere = `and p.output_status = :outputStatus`
        reqDatas.push(outputStatus)
    }
    if(signStatus === 0 || signStatus === 1) {
        signStatusWhere = `and p.sign_status = :signStatus`
        reqDatas.push(signStatus)
    }
    if(persongroup.length > 0){
        persongroupWhere = `and exists (select 1 from c##wxw.vue_xfjcusers u 
                                         where p.personid = u.personid
                                           and u.persongroup = :persongroup
                                        )                            
                            `
        reqDatas.push(persongroup)
    }else{
        persongroupWhere = ""
    }
    if(realname.length > 0){
        realnameWhere = `and p.displayname like '%' || :realname || '%'`
        reqDatas.push(realname)
    }else{
        realnameWhere = ""
    }
    // console.log(reqDatas)

    let sql = `
                select *
                from c##wxw.lb_plan p
                where p.year = :planYear
                ${quarterWhere}
                ${planStatusWhere}
                ${outputStatusWhere}
                ${signStatusWhere}
                ${persongroupWhere}
                ${realnameWhere}
                order by p.personid,p.lbnum
            `
            // console.log(sql)
    let result = reqDatas.length ? await db.search(sql,reqDatas) : await db.search2(sql)
    resMsg = result.errorNum ? `获取失败,${result}` : `获取成功`
    resStatus = result.errorNum ? 0 : 1
    resDatas = { result }
    res.json({
        msg:resMsg,
        status: resStatus,
        resDatas
    })
}

exports.editLbPlanInfo = async (req,res)=>{
    // console.log(req.body)
    let { type,form } = req.body
    let { ID,PERSONID,DISPLAYNAME,LBNUM,DESCRIPTION,QUINT,SERVICE_CYCLE,QUANTITY,
        PG_DESC,NEEDNUM,LINE_DESC,QUARTER,YEAR,CREATEDATE,PLAN_STATUS,
        OUTPUTDATE,OUTPUTQUANTITY,OUTPUT_STATUS,
        SIGNDATE,SIGNQUANTITY,SIGN_STATUS,SIGNBY,
        REMARK,PRODUCTDATE,SCHEDREDATE 
    } = form
    let reqData = []
    let msgType = ""
    let resMsg = ""
    let resStatus = 0
    let resDatas = {}
    let sql = ""

    if(type === 'add' || type === 'copy'){
        msgType = type === 'add' ? '添加' : '复制'
        let odCol = OUTPUTDATE !== null ? "OUTPUTDATE," : ""
        let oqCol = OUTPUTQUANTITY !== null ? "OUTPUTQUANTITY," : ""
        let sidCol = SIGNDATE !== null ? "SIGNDATE," : ""
        let sqCol = SIGNQUANTITY !== null ? "SIGNQUANTITY," : ""
        let sbCol = SIGNBY !== null ? "SIGNBY," : ""
        let rmCol = REMARK !== null ? "REMARK," : ""
        let pdCol = PRODUCTDATE !== null ? "PRODUCTDATE," : ""
        let scdCol = SCHEDREDATE !== null ? "SCHEDREDATE," : ""
        let odVal = OUTPUTDATE !== null ? `to_timestamp(:OUTPUTDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""
        let oqVal = OUTPUTQUANTITY !== null ? `:OUTPUTQUANTITY,` : ""
        let sidVal = SIGNDATE !== null ? `to_timestamp(:SIGNDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""
        let sqVal = SIGNQUANTITY !== null ? `:SIGNQUANTITY,` : ""
        let sbVal = SIGNBY !== null ? `:SIGNBY,` : ""
        let rmVal = REMARK !== null ? `:REMARK,` : ""
        let pdVal = PRODUCTDATE !== null ? `to_timestamp(:PRODUCTDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""
        let scdVal = SCHEDREDATE !== null ? `to_timestamp(:SCHEDREDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""        
        sql = `insert into c##wxw.lb_plan (PERSONID,DISPLAYNAME,LBNUM,DESCRIPTION,QUINT,SERVICE_CYCLE,QUANTITY,
                                            PG_DESC,NEEDNUM,LINE_DESC,QUARTER,YEAR,CREATEDATE,PLAN_STATUS,
                                            ${odCol}${oqCol}OUTPUT_STATUS,
                                            ${sidCol}${sqCol}${sbCol}${rmCol}${pdCol}${scdCol}SIGN_STATUS)
               values (:PERSONID,:DISPLAYNAME,:LBNUM,:DESCRIPTION,:QUINT,:SERVICE_CYCLE,:QUANTITY,
                        :PG_DESC,:NEEDNUM,:LINE_DESC,:QUARTER,:YEAR,to_timestamp(:CREATEDATE,'yyyy-mm-dd hh24:mi:ss.ff'),:PLAN_STATUS,
                        ${odVal}${oqVal}:OUTPUT_STATUS,
                        ${sidVal}${sqVal}${sbVal}${rmVal}${pdVal}${scdVal} :SIGN_STATUS
                        )
            `
        reqData = [PERSONID,DISPLAYNAME,LBNUM,DESCRIPTION,QUINT,SERVICE_CYCLE,QUANTITY,
                    PG_DESC,NEEDNUM,LINE_DESC,QUARTER,YEAR,CREATEDATE,PLAN_STATUS,
                    OUTPUTDATE,OUTPUTQUANTITY,OUTPUT_STATUS,
                    SIGNDATE,SIGNQUANTITY,SIGNBY,
                    REMARK,PRODUCTDATE,SCHEDREDATE,SIGN_STATUS].filter(item => item !== null)

        let reqSql = `select * from c##wxw.lb_plan 
                      where year = :YEAR 
                        and quarter = :QUARTER 
                        and lbnum = :LBNUM 
                        and personid = :PERSONID
                        and neednum = :NEEDNUM
                    `
        let lbListInfo = await db.search(reqSql,[YEAR,QUARTER,LBNUM,PERSONID,NEEDNUM])
        if(lbListInfo.length){
            res.json({
                msg:`新增失败，${YEAR}年${QUARTER},${PERSONID}的${LBNUM},需求单号：${NEEDNUM}已存在`,
                status:0
            })
            return
        }
    }
    if(type === 'update'){
        msgType = '更新'
        let odWhere = OUTPUTDATE !== null ? `outputdate = to_timestamp(:OUTPUTDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""
        let oqWhere = OUTPUTQUANTITY !== null ? `outputquantity = :OUTPUTQUANTITY,` : ""
        let sidWhere = SIGNDATE !== null ? `signdate = to_timestamp(:SIGNDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""
        let sqWhere = SIGNQUANTITY !== null ? `signquantity = :SIGNQUANTITY,` : ""
        let sbWhere = SIGNBY !== null ? `signby = :SIGNBY,` : ""
        let rmWhere = REMARK !== null ? `remark = :REMARK,` : ""
        let pdWhere = PRODUCTDATE !== null ? `productdate = to_timestamp(:PRODUCTDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""
        let scdWhere = SCHEDREDATE !== null ? `schedredate = to_timestamp(:SCHEDREDATE,'yyyy-mm-dd hh24:mi:ss.ff'),` : ""

        sql = `update c##wxw.lb_plan
               set personid = :PERSONID,
                   displayname = :DISPLAYNAME,
                   lbnum = :LBNUM,
                   description = :DESCRIPTION,
                   quint = :QUINT,
                   service_cycle = :SERVICE_CYCLE,
                   quantity = :QUANTITY,
                   pg_desc = :PG_DESC,
                   neednum = :NEEDNUM,
                   line_desc = :LINE_DESC,
                   quarter = :QUARTER,
                   year = :YEAR,
                   createdate = to_timestamp(:CREATEDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                   plan_status = :PLAN_STATUS,
                   ${odWhere}
                   ${oqWhere}
                   output_status = :OUTPUT_STATUS,
                   ${sidWhere}
                   ${sqWhere}
                   ${sbWhere}
                   ${rmWhere}
                   ${pdWhere}
                   ${scdWhere}
                   sign_status = :SIGN_STATUS
               where id = :ID
              `
        reqData = [PERSONID,DISPLAYNAME,LBNUM,DESCRIPTION,QUINT,SERVICE_CYCLE,QUANTITY,
                   PG_DESC,NEEDNUM,LINE_DESC,QUARTER,
                   YEAR,CREATEDATE,PLAN_STATUS,
                   OUTPUTDATE,OUTPUTQUANTITY,OUTPUT_STATUS,
                   SIGNDATE,SIGNQUANTITY,SIGNBY,
                   REMARK,PRODUCTDATE,SCHEDREDATE,SIGN_STATUS,ID].filter( item => item !== null)
        
    }
    if(type === 'delete'){
        msgType = '删除'
        sql = `delete from c##wxw.lb_plan
               where ID = :ID
              `
        reqData = [ID]
    }
    // console.log(sql,reqData)
    try{
        let result = await db.writeTab(sql,reqData)
        // console.log(result)
        resMsg = result.errorNum ? `${msgType}失败,${result}` : `${msgType}成功`
        resStatus = result.errorNum ? 0 : 1
        resDatas = { result }
        res.json({
            msg:resMsg,
            status: resStatus,
            resDatas
        })
    }catch(err){
        res.json({
            msg:`${msgType}失败`+err,
            status:0
        })
    }
}

exports.editManyLbPlanInfo = async (req,res)=>{     
    // console.log(req.body)
    let { type,form } = req.body
    let options = {
        autoCommit: true
    }
    let msgType = ""
    let resMsg = ""
    let resStatus = 0
    let resDatas = {}
    let sql = ""
    if(type === 'batchAdd'){
        msgType = '新增'
        sql = `insert into c##wxw.lb_plan (ID,PERSONID,DISPLAYNAME,LBNUM,DESCRIPTION,QUINT,SERVICE_CYCLE,QUANTITY,NEEDNUM,PG_DESC
                                            ,LINE_DESC,QUARTER,YEAR,CREATEDATE,PLAN_STATUS
                                            ,OUTPUTDATE,OUTPUTQUANTITY,OUTPUT_STATUS
                                            ,SIGNDATE,SIGNQUANTITY,SIGNBY,SIGN_STATUS
                                            ,REMARK,PRODUCTDATE,SCHEDREDATE
                                            )
               values (:ID,:PERSONID,:DISPLAYNAME,:LBNUM,:DESCRIPTION,:QUINT,:SERVICE_CYCLE,:QUANTITY,:NEEDNUM,:PG_DESC
                        ,:LINE_DESC,:QUARTER,:YEAR,to_timestamp(:CREATEDATE,'yyyy-mm-dd hh24:mi:ss.ff'),:PLAN_STATUS
                        ,:OUTPUTDATE,:OUTPUTQUANTITY,:OUTPUT_STATUS
                        ,:SIGNDATE,:SIGNQUANTITY,:SIGNBY,:SIGN_STATUS
                        ,:REMARK,:PRODUCTDATE,:SCHEDREDATE
                        )
              `
        //检测LB_PLAN表中是否存在相应的同一人员相同的年度、季度、需求申请号、物资编码的记录
        let needAddLps = []
        let oldLbPlanSql = `select p.lbnum
                            from c##wxw.lb_plan p
                            `
        let oldLbPlanInfo = await db.search2(oldLbPlanSql)
        let hasOlp = []
        form.forEach((item) => {
            if(oldLbPlanInfo.some(oldItem => {
                oldItem.PERSONID === item.PERSONID && oldItem.YEAR === item.YEAR 
                && oldItem.QUARTER === item.QUARTER && oldItem.NEEDNUM === item.NEEDNUM 
                && oldItem.LBNUM === item.LBNUM 
            })){
                hasOlp.push(item)
            }
        })
        // console.log(hasOlp)
        msgStr = ""
        hasOlp.forEach(item => {msgStr += `${item.PERSONID}:${item.YEAR}年${item.QUARTER}，需求单号为${item.NEEDNUM}的${item.LBNUM}；`})
        // console.log(msgStr)
        if(hasOlp.length){
            res.json({
                msg:`新增失败，${msgStr}均已存在`,
                status:0
            })
            return
        }
    }
    if(type === 'batchUpdate'){
        msgType = '更新'
        sql = `update c##wxw.lb_plan
               set PERSONID = :PERSONID,
                   DISPLAYNAME = :DISPLAYNAME,
                   LBNUM = :LBNUM,
                   DESCRIPTION = :DESCRIPTION,
                   QUINT = :QUINT,
                   SERVICE_CYCLE = :SERVICE_CYCLE,
                   QUANTITY = :QUANTITY,
                   PG_DESC = :PG_DESC,
                   NEEDNUM = :NEEDNUM,
                   LINE_DESC = :LINE_DESC,
                   QUARTER = :QUARTER,
                   YEAR = :YEAR,
                   CREATEDATE = to_timestamp(:CREATEDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                   PLAN_STATUS = :PLAN_STATUS,
                   OUTPUTDATE = to_timestamp(:OUTPUTDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                   OUTPUTQUANTITY = :OUTPUTQUANTITY,
                   OUTPUT_STATUS = :OUTPUT_STATUS,
                   SIGNDATE = to_timestamp(:SIGNDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                   SIGNQUANTITY = :SIGNQUANTITY,
                   SIGN_STATUS = :SIGN_STATUS,
                   SIGNBY = :SIGNBY,
                   REMARK = :REMARK,
                   PRODUCTDATE = to_timestamp(:PRODUCTDATE,'yyyy-mm-dd hh24:mi:ss.ff'),
                   SCHEDREDATE = to_timestamp(:SCHEDREDATE,'yyyy-mm-dd hh24:mi:ss.ff')
               where ID = :ID
              `
    }
    if(type === 'batchDel'){
        msgType = '删除'
        sql = `delete from c##wxw.lb_plan
               where ID = :ID
              `
    }
    // console.log(sql)
    try{
        let result = await db.writeManyTab(sql,form,options)
        resMsg = result.errorNum ? `${msgType}失败,${result}` : `${msgType}成功`
        resStatus = result.errorNum ? 0 : 1
        resDatas = { result }
        res.json({
            msg:resMsg,
            status: resStatus,
            resDatas
        })
    }catch(err){
        res.json({
            msg:`${msgType}失败`+err,
            status:0
        })
    }
}